#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import json
import pandas as pd
from datetime import datetime
import os

class HuzhouJsonToExcelConverter:
    """湖州地区JSON数据转Excel转换器"""
    
    def __init__(self, json_file_path):
        """初始化转换器"""
        self.json_file_path = json_file_path
        self.data = None
        self.load_data()
    
    def load_data(self):
        """加载JSON数据"""
        try:
            with open(self.json_file_path, 'r', encoding='utf-8') as f:
                self.data = json.load(f)
            print(f"✅ 成功加载数据文件: {self.json_file_path}")
        except Exception as e:
            print(f"❌ 加载数据文件失败: {e}")
            raise
    
    def get_data_summary(self):
        """获取数据摘要"""
        if not self.data:
            return
        
        # 统计各县市的数据
        districts = {}
        for area in self.data['residential_areas']:
            district = area['district_name']
            if district not in districts:
                districts[district] = {
                    'streets': set(),
                    'communities': set(),
                    'areas': 0
                }
            districts[district]['streets'].add(area['street_name'])
            districts[district]['communities'].add(area['community_name'])
            districts[district]['areas'] += 1
        
        return districts
    
    def organize_data_by_district(self):
        """按县市组织数据"""
        organized_data = {}
        
        for area in self.data['residential_areas']:
            district = area['district_name']
            street = area['street_name']
            
            if district not in organized_data:
                organized_data[district] = {}
            
            if street not in organized_data[district]:
                organized_data[district][street] = []
            
            organized_data[district][street].append(area)
        
        return organized_data
    
    def create_excel_file(self, output_filename=None):
        """创建Excel文件"""
        if not output_filename:
            timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
            output_filename = f"湖州地区JSON数据导出_{timestamp}.xlsx"
        
        # 获取数据摘要
        summary = self.get_data_summary()
        print("\n📊 数据摘要:")
        print()
        for district, info in summary.items():
            print(f"{district}:")
            print(f"  街道数量: {len(info['streets'])}")
            print(f"  社区数量: {len(info['communities'])}")
            print(f"  小区数量: {info['areas']}")
            print()
        
        # 组织数据
        organized_data = self.organize_data_by_district()
        
        print("开始生成Excel文件...")
        
        # 创建Excel写入器
        with pd.ExcelWriter(output_filename, engine='openpyxl') as writer:
            
            # 为每个县市创建一个工作表
            for district, streets_data in organized_data.items():
                print(f"正在处理 {district} 的数据...")
                
                # 创建该县市的数据列表
                district_rows = []
                
                # 按街道分组添加数据
                for street, areas in streets_data.items():
                    # 添加街道分组标题行
                    district_rows.append({
                        'ID': '',
                        '小区名称': f'【{street}】',
                        '地址': '',
                        '经纬度': '',
                        '社区名称': '',
                        '街道名称': '',
                        '县市名称': '',
                        'POI_ID': '',
                        '创建时间': ''
                    })
                    
                    # 添加该街道下的所有小区
                    for area in areas:
                        district_rows.append({
                            'ID': area['id'],
                            '小区名称': area['name'],
                            '地址': area['address'],
                            '经纬度': area['location'],
                            '社区名称': area['community_name'],
                            '街道名称': area['street_name'],
                            '县市名称': area['district_name'],
                            'POI_ID': area['poi_id'],
                            '创建时间': area['created_at']
                        })
                    
                    # 添加空行分隔
                    district_rows.append({
                        'ID': '',
                        '小区名称': '',
                        '地址': '',
                        '经纬度': '',
                        '社区名称': '',
                        '街道名称': '',
                        '县市名称': '',
                        'POI_ID': '',
                        '创建时间': ''
                    })
                
                # 创建DataFrame并写入工作表
                df = pd.DataFrame(district_rows)
                
                # 清理工作表名称（Excel工作表名称不能包含特殊字符）
                sheet_name = district.replace('/', '_').replace('\\', '_')
                if len(sheet_name) > 31:  # Excel工作表名称长度限制
                    sheet_name = sheet_name[:31]
                
                df.to_excel(writer, sheet_name=sheet_name, index=False)
                
                print(f"✅ {district} 数据处理完成")
        
        print(f"\n🎉 Excel文件已生成: {output_filename}")
        
        # 获取文件完整路径
        full_path = os.path.abspath(output_filename)
        print(f"\n✅ 导出完成!")
        print(f"📁 文件位置: {full_path}")
        
        print(f"\n📋 文件结构:")
        print(f"  • 每个县市一个工作表")
        print(f"  • 每个工作表内按街道分组")
        print(f"  • 包含小区的详细信息")
        
        return output_filename

def main():
    """主函数"""
    print("🏘️ 湖州地区JSON数据Excel导出器")
    print("=" * 50)
    
    # 检查JSON文件是否存在
    json_file = "data/huzhou_real_data.json"
    if not os.path.exists(json_file):
        print(f"❌ 找不到数据文件: {json_file}")
        return
    
    try:
        # 创建转换器
        converter = HuzhouJsonToExcelConverter(json_file)
        
        # 生成Excel文件
        output_file = converter.create_excel_file()
        
    except Exception as e:
        print(f"❌ 转换过程中发生错误: {e}")
        import traceback
        traceback.print_exc()

if __name__ == "__main__":
    main()